const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库连接配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'zyc188208',
  database: process.env.DB_NAME || 'login_system',
  port: process.env.DB_PORT || 3306,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
};

// 创建连接池
const pool = mysql.createPool(dbConfig);

// 测试数据库连接
const testConnection = async () => {
  try {
    const connection = await pool.getConnection();
    console.log('✅ 数据库连接成功');
    connection.release();
  } catch (error) {
    console.error('❌ 数据库连接失败:', error.message);
    process.exit(1);
  }
};

// 初始化数据库表
const initDatabase = async () => {
  try {
    const connection = await pool.getConnection();
    
    // 创建用户表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);

    // 检查是否已有管理员用户
    const [adminUsers] = await connection.execute(
      'SELECT * FROM users WHERE username = ?',
      ['admin']
    );

    // 如果没有管理员用户，创建一个默认的
    if (adminUsers.length === 0) {
      const bcrypt = require('bcryptjs');
      const hashedPassword = await bcrypt.hash('password', 10);
      
      await connection.execute(
        'INSERT INTO users (username, email, password) VALUES (?, ?, ?)',
        ['admin', 'admin@example.com', hashedPassword]
      );
      console.log('✅ 创建默认管理员账户: admin / password');
    }

    // 创建菜单表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS menu_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        parent_id INT DEFAULT NULL,
        label VARCHAR(64) NOT NULL,
        icon VARCHAR(32),
        path VARCHAR(128),
        order_num INT DEFAULT 0,
        visible TINYINT(1) DEFAULT 1
      )
    `);

    // 检查是否已有菜单数据
    const [menus] = await connection.execute('SELECT COUNT(*) as count FROM menu_items');
    if (menus[0].count === 0) {
      await connection.query(`
        INSERT INTO menu_items (parent_id, label, icon, path, order_num) VALUES
        (NULL, '仪表盘', '📊', 'dashboard', 1),
        (NULL, '工作台', '🛠️', 'work', 2),
        (NULL, '分析页', '📈', 'analysis', 3),
        (NULL, '电子商务', '🛒', 'ecommerce', 4),
        (NULL, '系统管理', '⚙️', 'system', 5),
        (5, '用户管理', '👤', 'system/users', 1),
        (5, '菜单管理', '📋', 'system/menus', 2)
      `);
      console.log('✅ 初始化菜单数据');
    }

    // 创建内容表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS content_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        video_url VARCHAR(512),
        content TEXT,
        images TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
    // 创建评论表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS content_comments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        content_id INT NOT NULL,
        author VARCHAR(100),
        comment TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (content_id) REFERENCES content_items(id) ON DELETE CASCADE
      )
    `);

    connection.release();
    console.log('✅ 数据库表初始化完成');
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error.message);
    throw error;
  }
};

module.exports = {
  pool,
  testConnection,
  initDatabase
}; 